Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 12 Using PL/SQL With Object Types

Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.

This chapter contains these topics:

- Declaring and Initializing Objects in PL/SQL

- Manipulating Objects in PL/SQL

- Defining SQL Types Equivalent to PL/SQL Collection Types

- Using PL/SQL Collections with SQL Object Types

- Using Dynamic SQL With Objects

For information about object types, see Oracle Database Application Developer's Guide - Object-Relational Features.

## Declaring and Initializing Objects in PL/SQL

An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list.

Currently, you cannot define object types in a PL/SQL block, subprogram, or package. You can define them interactively in SQL*Plus using the SQL statement `CREATE TYPE`. See Example 1-17, "Defining an Object Type".

For information on the `CREATE` `TYPE` SQL statement, see Oracle Database SQL Reference. For information on the `CREATE` `TYPE` `BODY` SQL statement, see Oracle Database SQL Reference.

After an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result. At run time, instances of the object type are created; that is, objects of that type are instantiated. Each object can hold different values.

Such objects follow the usual scope and instantiation rules. In a block or subprogram, local objects are instantiated when you enter the block or subprogram and cease to exist when you exit. In a package, objects are instantiated when you first reference the package and cease to exist when you end the database session.

Example 12-1 shows how to create an object type, object body type, and a table of object types.

Example 12-1 Working With Object Types

    CREATE TYPE address_typ AS OBJECT (
       street          VARCHAR2(30),
       city            VARCHAR2(20),
       state           CHAR(2),
       postal_code     VARCHAR2(6) );
    /
    CREATE TYPE employee_typ AS OBJECT (
      employee_id       NUMBER(6),
      first_name        VARCHAR2(20),
      last_name         VARCHAR2(25),
      email             VARCHAR2(25),
      phone_number      VARCHAR2(20),
      hire_date         DATE,
      job_id            VARCHAR2(10),
      salary            NUMBER(8,2),
      commission_pct    NUMBER(2,2),
      manager_id        NUMBER(6),
      department_id     NUMBER(4),
      address           address_typ,
      MAP MEMBER FUNCTION get_idno RETURN NUMBER,
      MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) );
    /
    CREATE TYPE BODY employee_typ AS
      MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
      BEGIN
        RETURN employee_id;
      END;
      MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE(first_name || ' '  || last_name);
        DBMS_OUTPUT.PUT_LINE(address.street);
        DBMS_OUTPUT.PUT_LINE(address.city || ', '  || address.state || ' ' ||
                             address.postal_code);
      END;
    END;
    /
    CREATE TABLE employee_tab OF employee_typ;

### Declaring Objects in a PL/SQL Block

You can use object types wherever built-in types such as `CHAR` or `NUMBER` can be used. In Example 12-2, you declare object `emp` of type `employee_typ`. Then, you call the constructor for object type `employee_typ` to initialize the object.

Example 12-2 Declaring Object Types in a PL/SQL Block

    DECLARE
      emp employee_typ; -- emp is atomically null
    BEGIN
    -- call the constructor for employee_typ
      emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
            '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,
             address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
      DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details
      emp.display_address();  -- call object method to display details
    END;
    /

You can declare objects as the formal parameters of functions and procedures. That way, you can pass objects to stored subprograms and from one subprogram to another. In the next example, you use object type `employee_typ` to specify the datatype of a formal parameter:

`PROCEDURE open_acct (new_acct IN OUT employee_typ) IS ...`

In the following example, you use object type `employee_typ` to specify the return type of a function:

`FUNCTION get_acct (acct_id IN NUMBER) RETURN employee_typ IS ...`

### How PL/SQL Treats Uninitialized Objects

Until you initialize an object by calling the constructor for its object type, the object is atomically null. That is, the object itself is null, not just its attributes.

A null object is never equal to another object. In fact, comparing a null object with any other object always yields `NULL`. Also, if you assign an atomically null object to another object, the other object becomes atomically null (and must be reinitialized). Likewise, if you assign the non-value `NULL` to an object, the object becomes atomically null.

In an expression, attributes of an uninitialized object evaluate to `NULL`. When applied to an uninitialized object or its attributes, the `IS` `NULL` comparison operator yields `TRUE`.

Example 12-3 illustrates null objects and objects with null attributes.

Example 12-3 Null Objects in a PL/SQL Block

    DECLARE
      emp employee_typ; -- emp is atomically null
    BEGIN
      IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #1'); END IF;
      IF emp.employee_id IS NULL THEN
         DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #1');
      END IF;
      emp.employee_id := 330;
      IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #2'); END IF;
      IF emp.employee_id IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #2');
      END IF;
      emp := employee_typ(NULL, NULL, NULL, NULL,
            NULL, NULL, NULL, NULL, NULL, NULL, NULL,
             address_typ(NULL, NULL, NULL, NULL));
      -- emp := NULL; -- this would have made the following IF statement TRUE
      IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #3'); END IF;
      IF emp.employee_id IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #3');
      END IF;
    EXCEPTION
       WHEN ACCESS_INTO_NULL THEN
         DBMS_OUTPUT.PUT_LINE('Cannot assign value to NULL object');
    END;
    /

The output is:

`emp is NULL #1`
`emp.employee_id is NULL #1`
`emp is NULL #2`
`emp.employee_id is NULL #3`

Calls to methods of an uninitialized object raise the predefined exception `NULL_SELF_DISPATCH`. When passed as arguments to `IN` parameters, attributes of an uninitialized object evaluate to `NULL`. When passed as arguments to `OUT` or `IN` `OUT` parameters, they raise an exception if you try to write to them.

## Manipulating Objects in PL/SQL

This section describes how to manipulate object attributes and methods in PL/SQL.

### Accessing Object Attributes With Dot Notation

You refer to an attribute by name. To access or change the value of an attribute, you use dot notation. Attribute names can be chained, which lets you access the attributes of a nested object type. For example:

Example 12-4 Accessing Object Attributes

    DECLARE
      emp employee_typ;
    BEGIN
      emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
            '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,
             address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
      DBMS_OUTPUT.PUT_LINE(emp.first_name || ' '  || emp.last_name);
      DBMS_OUTPUT.PUT_LINE(emp.address.street);
      DBMS_OUTPUT.PUT_LINE(emp.address.city || ', '  ||emp. address.state || ' ' ||
                           emp.address.postal_code);
    END;
    /

### Calling Object Constructors and Methods

Calls to a constructor are allowed wherever function calls are allowed. Like all functions, a constructor is called as part of an expression, as shown in Example 12-4 and Example 12-5.

Example 12-5 Inserting Rows in an Object Table

    DECLARE
      emp employee_typ;
    BEGIN
      INSERT INTO employee_tab VALUES (employee_typ(310, 'Evers', 'Boston', 'EBOSTON',
       '555.111.2222', '01-AUG-04', 'SA_REP', 9000, .15, 101, 110,
        address_typ('123 Main', 'San Francisco', 'CA', '94111')) );
      INSERT INTO employee_tab VALUES (employee_typ(320, 'Martha', 'Dunn', 'MDUNN',
        '555.111.3333', '30-SEP-04', 'AC_MGR', 12500, 0, 101, 110,
        address_typ('123 Broadway', 'Redwood City', 'CA', '94065')) );
    END;
    /

When you pass parameters to a constructor, the call assigns initial values to the attributes of the object being instantiated. When you call the default constructor to fill in all attribute values, you must supply a parameter for every attribute; unlike constants and variables, attributes cannot have default values. You can call a constructor using named notation instead of positional notation.

Like packaged subprograms, methods are called using dot notation. In Example 12-6, the `display_address` method is called to display attributes of an object. Note the use of the `VALUE` function which returns the value of an object. `VALUE` takes as its argument a correlation variable. In this context, a correlation variable is a row variable or table alias associated with a row in an object table.

Example 12-6 Accessing Object Methods

    DECLARE
      emp employee_typ;
    BEGIN
      SELECT VALUE(e) INTO emp FROM employee_tab e WHERE e.employee_id = 310;
      emp.display_address();
    END;
    /

In SQL statements, calls to a parameterless method require an empty parameter list. In procedural statements, an empty parameter list is optional unless you chain calls, in which case it is required for all but the last call. You cannot chain additional method calls to the right of a procedure call because a procedure is called as a statement, not as part of an expression. Also, if you chain two function calls, the first function must return an object that can be passed to the second function.

For static methods, calls use the notation `type_name``.``method_name` rather than specifying an instance of the type.

When you call a method using an instance of a subtype, the actual method that is executed depends on the exact declarations in the type hierarchy. If the subtype overrides the method that it inherits from its supertype, the call uses the subtype's implementation. Or, if the subtype does not override the method, the call uses the supertype's implementation. This capability is known as dynamic method dispatch.

Note: When implementing methods using PL/SQL, you cannot call a base or supertype object method with the `super` keyword or an equivalent method in a derived object. See Oracle Database Application Developer's Guide - Object-Relational Features for additional information on supertypes, subtypes, and object methods.

### Updating and Deleting Objects

From inside a PL/SQL block you can modify and delete rows in an object table.

Example 12-7 Updating and Deleting Rows in an Object Table

    DECLARE
      emp employee_typ;
    BEGIN
      INSERT INTO employee_tab VALUES (employee_typ(370, 'Robert', 'Myers', 'RMYERS',
       '555.111.2277', '07-NOV-04', 'SA_REP', 8800, .12, 101, 110,
        address_typ('540 Fillmore', 'San Francisco', 'CA', '94011')) );
      UPDATE employee_tab e SET e.address.street = '1040 California'
         WHERE e.employee_id = 370;
      DELETE FROM employee_tab e WHERE e.employee_id = 310;
    END;
    /

### Manipulating Objects Through Ref Modifiers

You can retrieve refs using the function `REF`, which takes as its argument a correlation variable.

Example 12-8 Updating Rows in an Object Table With a REF Modifier

DECLARE emp employee_typ; emp_ref REF employee_typ; BEGIN SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.employee_id = 370; UPDATE employee_tab e SET e.address = address_typ('8701 College', 'Oakland', 'CA', '94321') WHERE REF(e) = emp_ref; END; /

You can declare refs as variables, parameters, fields, or attributes. You can use refs as input or output variables in SQL data manipulation statements.

You cannot navigate through refs in PLSQL. For example, the assignment in Example 12-9 using a ref is not allowed. Instead, use the function `DEREF` or make calls to the package `UTL_REF` to access the object. For information on the `REF` function, see Oracle Database SQL Reference.

Example 12-9 Using DEREF in a SELECT INTO Statement

    DECLARE
      emp           employee_typ;
      emp_ref   REF employee_typ;
      emp_name      VARCHAR2(50);
    BEGIN
      SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.employee_id = 370;
    -- the following assignment raises an error, not allowed in PL/SQL
    -- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
    -- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
      SELECT DEREF(emp_ref) INTO emp FROM DUAL; -- use dummy table DUAL
      emp_name := emp.first_name || ' ' || emp.last_name;
      DBMS_OUTPUT.PUT_LINE(emp_name);
    END;
    /

For information on the `DEREF` function, see Oracle Database SQL Reference.

## Defining SQL Types Equivalent to PL/SQL Collection Types

To store nested tables and varrays inside database tables, you must also declare SQL types using the `CREATE TYPE` statement. The SQL types can be used as columns or as attributes of SQL object types. For information on the `CREATE` `TYPE` SQL statement, see Oracle Database SQL Reference. For information on the `CREATE` `TYPE` `BODY` SQL statement, see Oracle Database SQL Reference. For more information on object types, see Oracle Database Application Developer's Guide - Object-Relational Features.

You can declare equivalent types within PL/SQL, or use the SQL type name in a PL/SQL variable declaration.

Example 12-10 shows how you might declare a nested table in SQL, and use it as an attribute of an object type.

Example 12-10 Declaring a Nested Table in SQL

    CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)  -- define type
    /
    CREATE TYPE student AS OBJECT (  -- create object
       id_num  INTEGER(4),
       name    VARCHAR2(25),
       address VARCHAR2(35),
       status  CHAR(2),
       courses CourseList);  -- declare nested table as attribute
    /
    CREATE TABLE sophomores of student
      NESTED TABLE courses STORE AS courses_nt;

The identifier `courses` represents an entire nested table. Each element of `courses` stores the name of a college course such as `'Math 1020'`.

Example 12-11 creates a database column that stores varrays. Each varray element contains a `VARCHAR2`.

Example 12-11 Creating a Table with a Varray Column

    -- Each project has a 16-character code name.
    -- We will store up to 50 projects at a time in a database column.
    CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
    /
    CREATE TABLE dept_projects (  -- create database table
       dept_id  NUMBER(2),
       name     VARCHAR2(15),
       budget   NUMBER(11,2),
    -- Each department can have up to 50 projects.
       projects ProjectList);

In Example 12-12, you insert a row into database table `dept_projects`. The varray constructor `ProjectList()` provides a value for column `projects`.

Example 12-12 Varray Constructor Within a SQL Statement

    BEGIN
      INSERT INTO dept_projects
        VALUES(60, 'Security', 750400,
          ProjectList('New Badges', 'Track Computers', 'Check Exits'));
    END;
    /

In Example 12-13, you insert several scalar values and a `CourseList` nested table into the `sophomores` table.

Example 12-13 Nested Table Constructor Within a SQL Statement

    CREATE TABLE sophomores of student
      NESTED TABLE courses STORE AS courses_nt;
    BEGIN
       INSERT INTO sophomores
          VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT',
             CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
    END;
    /

### Manipulating Individual Collection Elements with SQL

By default, SQL operations store and retrieve whole collections rather than individual elements. To manipulate the individual elements of a collection with SQL, use the `TABLE` operator. The `TABLE` operator uses a subquery to extract the varray or nested table, so that the `INSERT`, `UPDATE`, or `DELETE` statement applies to the nested table rather than the top-level table.

To perform DML operations on a PL/SQL nested table, use the operators `TABLE` and `CAST`. This way, you can do set operations on nested tables using SQL notation, without actually storing the nested tables in the database.

The operands of `CAST` are PL/SQL collection variable and a SQL collection type (created by the `CREATE TYPE` statement). `CAST` converts the PL/SQL collection to the SQL type.

Example 12-14 Performing Operations on PL/SQL Nested Tables With CAST

    CREATE TYPE Course AS OBJECT
               (course_no  NUMBER,
                title      VARCHAR2(64),
                credits    NUMBER);
    /
    CREATE TYPE CourseList AS TABLE OF course;
    /

    -- create department table
    CREATE TABLE department (
       name     VARCHAR2(20),
       director VARCHAR2(20),
       office   VARCHAR2(20),
       courses  CourseList)
       NESTED TABLE courses STORE AS courses_tab;

    INSERT INTO department VALUES ('English', 'June Johnson', '491C',
                     CourseList(Course(1002, 'Expository Writing', 4),
                     Course(2020, 'Film and Literature', 4),
                     Course(4210, '20th-Century Poetry', 4),
                     Course(4725, 'Advanced Workshop in Poetry', 4)));

    DECLARE
       revised CourseList :=
          CourseList(Course(1002, 'Expository Writing', 3),
                     Course(2020, 'Film and Literature', 4),
                     Course(4210, '20th-Century Poetry', 4),
                     Course(4725, 'Advanced Workshop in Poetry', 5));
       num_changed INTEGER;
    BEGIN
       SELECT COUNT(*) INTO num_changed
          FROM TABLE(CAST(revised AS CourseList)) new,
          TABLE(SELECT courses FROM department
             WHERE name = 'English') old
          WHERE new.course_no = old.course_no AND
             (new.title != old.title OR new.credits != old.credits);
       DBMS_OUTPUT.PUT_LINE(num_changed);
    END;
    /

## Using PL/SQL Collections with SQL Object Types

Collections let you manipulate complex datatypes within PL/SQL. Your program can compute subscripts to process specific elements in memory, and use SQL to store the results in database tables.

In SQL*Plus, you can create SQL object types whose definitions correspond to PL/SQL nested tables and varrays, as shown in Example 12-15. Each item in column `dept_names` is a nested table that will store the department names for a specific region. The `NESTED` `TABLE` clause is required whenever a database table has a nested table column. The clause identifies the nested table and names a system-generated store table, in which Oracle stores the nested table data.

Within PL/SQL, you can manipulate the nested table by looping through its elements, using methods such as `TRIM` or `EXTEND`, and updating some or all of the elements. Afterwards, you can store the updated table in the database again. You can insert table rows containing nested tables, update rows to replace its nested table, and select nested tables into PL/SQL variables. You cannot update or delete individual nested table elements directly with SQL; you have to select the nested table from the table, change it in PL/SQL, then update the table to include the new nested table.

Example 12-15 Using INSERT, UPDATE, DELETE, and SELECT Statements With Nested Tables

    CREATE TYPE dnames_tab AS TABLE OF VARCHAR2(30);
    /
    CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_tab)
       NESTED TABLE dept_names STORE AS dnames_nt;
    BEGIN
       INSERT INTO depts VALUES('Europe', dnames_tab('Shipping','Sales','Finance'));
       INSERT INTO depts VALUES('Americas', dnames_tab('Sales','Finance','Shipping'));
       INSERT INTO depts VALUES('Asia', dnames_tab('Finance','Payroll'));
       COMMIT;
    END;
    /
    DECLARE
    -- Type declaration is not needed, because PL/SQL can access the SQL object type
    -- TYPE dnames_tab IS TABLE OF VARCHAR2(30); not needed
    -- Declare a variable that can hold a set of department names
       v_dnames dnames_tab;
    -- Declare a record that can hold a row from the table
    -- One of the record fields is a set of department names
       v_depts depts%ROWTYPE;
       new_dnames dnames_tab;
    BEGIN
    -- Look up a region and query just the associated department names
       SELECT dept_names INTO v_dnames FROM depts WHERE region = 'Europe';
       FOR i IN v_dnames.FIRST .. v_dnames.LAST
       LOOP
          DBMS_OUTPUT.PUT_LINE('Department names: ' || v_dnames(i));
       END LOOP;
    -- Look up a region and query the entire row
       SELECT * INTO v_depts FROM depts WHERE region = 'Asia';
    -- Now dept_names is a field in a record, so we access it with dot notation
       FOR i IN v_depts.dept_names.FIRST .. v_depts.dept_names.LAST LOOP
    -- Because we have all the table columns in the record, we can refer to region
         DBMS_OUTPUT.PUT_LINE(v_depts.region || ' dept_names = ' ||
                              v_depts.dept_names(i));
       END LOOP;
    -- We can replace a set of department names with a new collection
    -- in an UPDATE statement
       new_dnames := dnames_tab('Sales','Payroll','Shipping');
       UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe';
    -- Or we can modify the original collection and use it in the UPDATE.
    -- We'll add a new final element and fill in a value
       v_depts.dept_names.EXTEND(1);
       v_depts.dept_names(v_depts.dept_names.COUNT) := 'Finance';
       UPDATE depts SET dept_names = v_depts.dept_names
         WHERE region = v_depts.region;
    -- We can even treat the nested table column like a real table and
    -- insert, update, or delete elements. The TABLE operator makes the statement
    -- apply to the nested table produced by the subquery.
       INSERT INTO TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
         VALUES('Sales');
       DELETE FROM TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
          WHERE column_value = 'Payroll';
       UPDATE TABLE(SELECT dept_names FROM depts WHERE region = 'Americas')
          SET column_value = 'Payroll' WHERE column_value = 'Finance';
       COMMIT;
    END;
    /

Example 12-16 shows how you can manipulate SQL varray object types with PL/SQL statements. In this example, varrays are transferred between PL/SQL variables and SQL tables. You can insert table rows containing varrays, update a row to replace its varray, and select varrays into PL/SQL variables. You cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray.

Example 12-16 Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays

    -- By using a varray, we put an upper limit on the number of elements
    -- and ensure they always come back in the same order
    CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
    /
    CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
    BEGIN
       INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
       INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
       INSERT INTO depts
         VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
       COMMIT;
    END;
    /
    DECLARE
       new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting',
                                           'Executive', 'Marketing');
       some_dnames dnames_var;
    BEGIN
       UPDATE depts SET dept_names  = new_dnames WHERE region = 'Europe';
       COMMIT;
       SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe';
       FOR i IN some_dnames.FIRST .. some_dnames.LAST
       LOOP
          DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i));
       END LOOP;
    END;
    /

In Example 12-17, PL/SQL `BULK` `COLLECT` is used with a multilevel collection that includes an object type.

Example 12-17 Using BULK COLLECT with Nested Tables

    CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
    /
    CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
    BEGIN
       INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
       INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
       INSERT INTO depts
         VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
       COMMIT;
    END;
    /
    DECLARE
       TYPE dnames_tab IS TABLE OF dnames_var;
       v_depts dnames_tab;
    BEGIN
        SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
        DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
    END;
    /

## Using Dynamic SQL With Objects

Example 12-18 illustrates the use of objects and collections with dynamic SQL. First, define object type `person_typ` and `VARRAY` type `hobbies_var`, then write a package that uses these types.

Example 12-18 TEAMS Package Using Dynamic SQL for Object Types and Collections

    CREATE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);
    /
    CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);
    /
    CREATE OR REPLACE PACKAGE teams
       AUTHID CURRENT_USER AS
       PROCEDURE create_table (tab_name VARCHAR2);
       PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);
       PROCEDURE print_table (tab_name VARCHAR2);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY teams AS
       PROCEDURE create_table (tab_name VARCHAR2) IS
       BEGIN
          EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
                            ' (pers person_typ, hobbs hobbies_var)';
       END;
       PROCEDURE insert_row (
          tab_name VARCHAR2,
          p person_typ,
          h hobbies_var) IS
       BEGIN
          EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
             ' VALUES (:1, :2)' USING p, h;
       END;
       PROCEDURE print_table (tab_name VARCHAR2) IS
          TYPE  refcurtyp IS REF CURSOR;
          v_cur refcurtyp;
          p     person_typ;
          h     hobbies_var;
       BEGIN
          OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;
          LOOP
             FETCH v_cur INTO p, h;
             EXIT WHEN v_cur%NOTFOUND;
             -- print attributes of 'p' and elements of 'h'
             DBMS_OUTPUT.PUT_LINE('Name: ' || p.name || ' - Age: ' || p.age);
             FOR i IN h.FIRST..h.LAST
             LOOP
               DBMS_OUTPUT.PUT_LINE('Hobby(' || i || '): ' || h(i));
             END LOOP;
          END LOOP;
          CLOSE v_cur;
       END;
    END;
    /

From an anonymous block, you might call the procedures in package `TEAMS`:

Example 12-19 Calling Procedures from the TEAMS Package

    DECLARE
       team_name VARCHAR2(15);
    BEGIN
       team_name := 'Notables';
       TEAMS.create_table(team_name);
       TEAMS.insert_row(team_name, person_typ('John', 31),
          hobbies_var('skiing', 'coin collecting', 'tennis'));
       TEAMS.insert_row(team_name, person_typ('Mary', 28),
          hobbies_var('golf', 'quilting', 'rock climbing', 'fencing'));
       TEAMS.print_table(team_name);
    END;
    /